package com.shms.dao.impl;

import java.util.ArrayList;
import java.util.List;

import org.hibernate.Hibernate;
import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import com.shms.bean.GroupOfClass;
import com.shms.dao.ShClassDao;
import com.shms.entity.ShClass;
import com.shms.entity.ShStudent;
import com.shms.until.PageUntil;

/**
 * 文件名：ShClassDaoImpl.java
 * 编写者：
 * 创建时间：2013-1-3
 * 简要描述：
 *
 */
public class ShClassDaoImpl extends HibernateDaoSupport implements ShClassDao {

	@SuppressWarnings("finally")
	@Override
	public GroupOfClass queryClass(GroupOfClass groupOfClass) {
		GroupOfClass gOfClassRtn = new GroupOfClass();
		try {
			//查询数据总条数的SQL语句
			String sql = "SELECT COUNT(*) FROM SH_CLASS";
			SQLQuery sqlQuery = this.getSessionFactory().getCurrentSession().createSQLQuery(sql);
			//获取总条数
			int allRow = Integer.parseInt(String.valueOf(sqlQuery.uniqueResult()));
			//获取总页数
			int totalPage = PageUntil.countTotalPage(groupOfClass.getPageSize(), allRow);
			//获取要查询显示的数据的页数
			int currentPage = PageUntil.countCurrentPage(groupOfClass.getCurrentPage(), totalPage);
			//获取要查询显示的数据的第一条的数字
			int currentRow = PageUntil.countCurrentRow(groupOfClass.getPageSize(), currentPage);
			
			sql = "SELECT C.CID AS cid,C.CNAME As cname, G.GNAME as park1 FROM SH_CLASS C, SH_GRADE G WHERE C.GID=G.GID";
			sqlQuery = this.getSessionFactory().getCurrentSession().createSQLQuery(sql);
			sqlQuery.addScalar("cid",Hibernate.INTEGER).addScalar("cname", Hibernate.STRING).addScalar("park1", Hibernate.STRING);
			//查询结果转换为对象
			sqlQuery.setResultTransformer(Transformers.aliasToBean(ShClass.class));
			//设置查询的第一条数据位置
			sqlQuery.setFirstResult(currentRow);
			//设置查询最大条数
			sqlQuery.setMaxResults(groupOfClass.getPageSize());
			@SuppressWarnings("unchecked")
			List<ShClass> classesListRtn = sqlQuery.list();
			gOfClassRtn = new GroupOfClass(currentPage, totalPage, allRow, classesListRtn); 
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			return gOfClassRtn;
		}
	}

	@SuppressWarnings("finally")
	@Override
	public boolean batchDelete(Integer[] ids) {
		boolean rtn = true;
		try {
			String sql = "DELETE FROM SH_CLASS C WHERE C.CID NOT IN (SELECT C.CID FROM SH_CLASS C,SH_STUDENT S WHERE S.CID=C.CID) AND C.CID IN (:ids)";
			SQLQuery sqlQuery = this.getSessionFactory().getCurrentSession().createSQLQuery(sql);
			sqlQuery.setParameterList("ids", ids);
			sqlQuery.executeUpdate();
		} catch (Exception ex) {
			ex.printStackTrace();
			rtn = false;
		} finally {
			return rtn;
		}
	}

	@SuppressWarnings("finally")
	@Override
	public boolean updateClasses(ShClass shClass) {
		boolean rtn = true;
		try {
			this.getSessionFactory().getCurrentSession().update(shClass);
		} catch (Exception ex) {
			ex.printStackTrace();
			rtn = false;
		} finally {
			return rtn;
		}
	}

	@SuppressWarnings("finally")
	@Override
	public boolean saveClasses(ShClass shClass) {
		boolean rtn = true;
		try {
			this.getSessionFactory().getCurrentSession().save(shClass);
		} catch (Exception ex) {
			ex.printStackTrace();
			rtn = false;
		} finally {
			return rtn;
		}
	}
	
	@SuppressWarnings({ "unchecked", "finally" })
	public List<ShClass> queryAllCname(){
		List<ShClass> shc = new ArrayList<ShClass>();
		try {
			String sql = "SELECT * FROM SH_CLASS";
			SQLQuery sqlQuery = this.getSessionFactory().getCurrentSession().createSQLQuery(sql);
			shc = sqlQuery.addEntity(ShClass.class).list();
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			return shc;
		}
	}
	
	@SuppressWarnings("finally")
	public ShClass queryById(ShClass shClass) {
		ShClass shClassRtn = new ShClass();
		try {
			String sql = "SELECT * FROM SH_CLASS WHERE CID = :cid";
			SQLQuery sqlQuery = this.getSessionFactory().getCurrentSession().createSQLQuery(sql);
			sqlQuery.setProperties(shClass);
			@SuppressWarnings("unchecked")
			List<ShClass> listClass = sqlQuery.addEntity(ShClass.class).list();
			if (listClass.size() == 1) {
				shClassRtn = listClass.get(0);
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			return shClassRtn;
		}
	}

	@SuppressWarnings({ "unchecked", "finally" })
	@Override
	public List<ShStudent> queryStudentsById(ShClass shClass) {
		List<ShStudent> shc = new ArrayList<ShStudent>();
		try {
			String sql = "SELECT S.* FROM SH_STUDENT S,SH_CLASS C WHERE S.CID=C.CID AND C.CID = :cid";
			SQLQuery sqlQuery = this.getSessionFactory().getCurrentSession().createSQLQuery(sql);
			sqlQuery.setProperties(shClass);
			shc = sqlQuery.addEntity(ShStudent.class).list();
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			return shc;
		}
	}
}
